﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Persistency.Handler;
using System.Data.SqlClient;
using System.Data;

namespace Persistency.Misc
{
	public class DBHelper
	{

		#region Singleton

		private static DBHelper instance = null;

		private DBHelper() { }

		public static DBHelper Instance
		{
			get
			{
				if (instance == null)
				{
					instance = new DBHelper();
				}
				return instance;
			}
		}

		#endregion

		public object ExecuteScalar(string sqlString)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				SqlCommand sqlCommand = new SqlCommand(sqlString, handler.Connection);
				return sqlCommand.ExecuteScalar();
			}
		}

		public object ExecuteScalar(SqlCommand sqlCommand)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				sqlCommand.Connection = handler.Connection;
				return sqlCommand.ExecuteScalar();
			}
		}

		public int ExecuteNonQuery(string sqlString)
		{
			return ExecuteNonQuery(sqlString, false);
		}

		public int ExecuteNonQuery(string sqlString, bool longConnectionTimeout)
		{
			using (ConnectionHandler handler = new ConnectionHandler(longConnectionTimeout))
			{
				SqlCommand sqlCommand = new SqlCommand(sqlString, handler.Connection);
				sqlCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				return sqlCommand.ExecuteNonQuery();
			}
		}

		public int ExecuteNonQuery(SqlCommand sqlCommand)
		{
			return ExecuteNonQuery(sqlCommand, false);
		}

		public int ExecuteNonQuery(SqlCommand sqlCommand, bool longConnectionTimeout)
		{
			using (ConnectionHandler handler = new ConnectionHandler(longConnectionTimeout))
			{
				sqlCommand.Connection = handler.Connection;
				sqlCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				return sqlCommand.ExecuteNonQuery();
			}
		}

		public IAsyncResult ExecuteNonQueryAsync(string sqlString)
		{
			return ExecuteNonQueryAsync(sqlString, false);
		}

		public IAsyncResult ExecuteNonQueryAsync(string sqlString, bool longConnectionTimeout)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				if (handler.Connection.State == ConnectionState.Open)
				{
					throw new Exception("There is already an open database connection!");
				}
				handler.Connection.ConnectionString += ";Asynchronous Processing=true";
				SqlCommand sqlCommand = new SqlCommand(sqlString, handler.Connection);
				sqlCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				return sqlCommand.BeginExecuteNonQuery();
			}
		}

		public IAsyncResult ExecuteNonQueryAsync(SqlCommand sqlCommand)
		{
			return ExecuteNonQueryAsync(sqlCommand, false);
		}

		public IAsyncResult ExecuteNonQueryAsync(SqlCommand sqlCommand, bool longConnectionTimeout)
		{
			using (ConnectionHandler handler = new ConnectionHandler(longConnectionTimeout))
			{
				if (handler.Connection.State == ConnectionState.Open)
				{
					throw new Exception("There is already an open database connection!");
				}

				handler.Connection.ConnectionString += ";Asynchronous Processing=true";
				sqlCommand.Connection = handler.Connection;
				sqlCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				return sqlCommand.BeginExecuteNonQuery();
			}
		}

		public DataTable GetDataTable(string sqlCommand)
		{
			return GetDataTable(sqlCommand, false);
		}

		public DataTable GetDataTable(string sqlCommand, bool blankRow)
		{
			DataTable returnTable = new DataTable();

#if MULTITHREADING
            Monitor.Enter(Utils.lockVar);
#endif
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand, handler.Connection);
				adapter.SelectCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				adapter.Fill(returnTable);
			}
#if MULTITHREADING
            Monitor.Exit(Utils.lockVar);
#endif

			if (blankRow)
			{
				returnTable.Rows.InsertAt(returnTable.NewRow(), 0);
			}

			return returnTable;
		}

		public DataTable GetDataTable(SqlCommand sqlCommand)
		{
			DataTable returnTable = new DataTable();

#if MULTITHREADING
            Monitor.Enter(Utils.lockVar);
#endif
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				sqlCommand.Connection = handler.Connection;

				SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
				sqlCommand.CommandTimeout = handler.Connection.ConnectionTimeout;
				adapter.Fill(returnTable);
			}
#if MULTITHREADING
            Monitor.Exit(Utils.lockVar);
#endif

			return returnTable;
		}

		public DataTable GetDataTable(SqlCommand sqlCommand, bool blankRow)
		{
			DataTable returnTable = GetDataTable(sqlCommand);

			returnTable.Rows.InsertAt(returnTable.NewRow(), 0);

			return returnTable;
		}

		public DataTable GetDataTable(SqlCommand sqlCommand, string allText)
		{
			DataTable returnTable = GetDataTable(sqlCommand);
			DataRow allRow = returnTable.NewRow();

			#region Prepare new row

			foreach (DataColumn column in returnTable.Columns)
			{
				if (column.DataType == typeof(string))
				{
					allRow[column] = allText;
				}
			}

			#endregion

			returnTable.Rows.InsertAt(allRow, 0);

			return returnTable;
		}

		public bool QueryHasRows(string sqlString)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				SqlCommand sqlCommand = new SqlCommand(sqlString, handler.Connection);
				return sqlCommand.ExecuteReader().HasRows;
			}
		}

		public bool QueryHasRows(SqlCommand sqlCommand)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				sqlCommand.Connection = handler.Connection;
				return sqlCommand.ExecuteReader().HasRows;
			}
		}

		public DataTable GetSchema(string table)
		{
			using (ConnectionHandler handler = new ConnectionHandler())
			{
				return handler.Connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { handler.Connection.Database, "dbo", table });
			}
		}
	}
}
